using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using BCB.IDAL;
using BCB.Model;
using BCB.DBUtility;

namespace BCB.SQLServerDAL
{
    public class Category : ICategory
    {
        private const string SQL_SELECT_ALL_CATEGORY = "SELECT [CategoryID],[Name],[Description] FROM [Category] WHERE [ParentID] IS NULL";
        private const string SQL_SELECT_ALL_SUBCATEGORY_BY_MAINCATEGORY = "SELECT [CategoryID],[Name],[Description] FROM [Category] WHERE [ParentID] = @ParentId";
        
        public List<CategoryInfo> GetAllMainCategory()
        {
            List<CategoryInfo> mainCategories = new List<CategoryInfo>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.BCBConnectionString, CommandType.Text, SQL_SELECT_ALL_CATEGORY))
            {
                while (reader.Read())
                {
                    mainCategories.Add(fetch(reader));
                }
            }
            return mainCategories;
        }

        public List<CategoryInfo> GetALLSubCategoryByMaincategory(int mainCategoryId)
        {
            List<CategoryInfo> subCategoryies = new List<CategoryInfo>();

            SqlParameter para = new SqlParameter("@ParentId", SqlDbType.Int);
            para.Value = mainCategoryId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.BCBConnectionString, CommandType.Text,
                SQL_SELECT_ALL_SUBCATEGORY_BY_MAINCATEGORY, para))
            {
                while (reader.Read())
                {
                    subCategoryies.Add(fetch(reader));
                }
            }

            return subCategoryies;
        }

        private CategoryInfo fetch(SqlDataReader reader)
        {
            CategoryInfo category = new CategoryInfo();

            category.CategoryId = reader.GetInt32(0);
            category.Name = reader.GetString(1);

            if (reader.IsDBNull(2))
            {
                category.Description = string.Empty;
            }
            else
            {
                category.Description = reader.GetString(2);
            }
            return category;
        }
    }
}
